package com.ssm.changeunit.persistance;

import java.sql.*;

/**
 * "Project" Table을 조작하는 DAO
 * 
 * @author 시리우스
 */
public class ProjectDAO extends AbstractProjectDAO {
    /**
     * 검색
     * 
     * @param projectId
     * @return
     */
    @Override
    public ProjectDTO find() throws PersistanceException {
        ProjectDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select PROJECTID from APP.PROJECT";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()){
                dto = new ProjectDTO();
                dto.setProjectId(rs.getString("PROJECTID"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ProjectDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }
    
    @Override
    public ProjectDTO find(String projectId) throws PersistanceException {
        ProjectDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select PROJECTID from APP.PROJECT where PROJECTID = '"+projectId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()){
                dto = new ProjectDTO();
                dto.setProjectId(rs.getString("PROJECTID"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ProjectDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }

    /**
     * 삽입
     * 
     * @param projectDTO
     */
    @Override
    public void insert(ProjectDTO projectDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="insert into APP.PROJECT (PROJECTID ) " 
//                    + "values ('"+projectDTO.getProjectId()+"')";
        
        String sql="insert into APP.PROJECT (PROJECTID ) " 
                    + "values (?)";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, projectDTO.getProjectId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ProjectDAO.insert: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }
    }
 
    /**
     * 업데이트
     * 
     * @param projectDTO
     */
    @Override
    public void update(ProjectDTO projectDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
        //String sql="update APP.PROJECT set PROJECTID= '"+ projectDTO.getProjectId()+ "'";
        
        String sql="update APP.PROJECT set PROJECTID= ?";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, projectDTO.getProjectId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ProjectDAO.update: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }     
    }
  
    /**
     * 삭제
     * 
     * @param projectId
     */
    @Override
    public void delete(String projectId) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement 
        String sql="delete from APP.PROJECT "  
                   +"WHERE PROJECTID = '"+projectId+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ProjectDAO.delete: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }
}
